<?php
namespace app\admin\controller;
use cmf\controller\AdminBaseController;
use think\Db;
use think\Loader;
use think\Request;
use think\paginator\driver\Bootstrap;
use app\admin\model\LogisticsExcelModel;

class AsiabillController extends AdminBaseController
{

    public function _initialize()
    {
        parent::_initialize();
    }

    //店小秘添加
    public function dxmExcelAdd()
    {
        if($this->request->isPost()){

            $excel_name = $this->request->param('excel_name');
            $excel_url = $this->request->param('excel_url');

            if(!empty($excel_name) && !empty($excel_url)){

                $excel_array = $this->excelRead($excel_url);

                if ($excel_array[0][0] != '买家账号' && $excel_array[0][1] != '买家Email') {
                    $this->error('确认表格是否正确');
                }

                array_shift($excel_array);  //删除第一个数组(标题);

                //查库，去掉重复录入的数据
                $mysql_data = Db::name('dxm_excel')->field('order_num')->select()->toArray();
                if ($mysql_data) {
                    foreach ($mysql_data as $k => $v) {
                        $all_data[] = $v['order_num'];
                    }
                }

                //关联物流数据
                $mysql_logistics = Db::name('logistics_excel')->field('name,url')->select()->toArray();               
                foreach ($mysql_logistics as $key => $value) {
                    $logistics_data[$value['name']] = $value['url'];
                }


                foreach ($excel_array as $key => $value) {
                    //已有数据去掉
                    if (isset($all_data) && in_array($value[7], $all_data)) {
                        continue;
                    }
                    if ($value[3] == '') {
                        continue;
                    }			

                    $data[] = array(
                        'order_num'         => $value[7],
                        'order_price'       => $value[6],
                        'buyer_account'     => $value[0],
                        'buyer_email'       => $value[1],
                        'shipping'          => isset($logistics_data[$value[4]]) ? $logistics_data[$value[4]] : '',
                        'logistics_number'  => $value[3],
                    );
                }           

                if (isset($data)) {
                    Db::name('dxm_excel')->insertAll($data);
                    $this->success('导入成功' , url("asiabill/index"));
                }else{
                    $this->error('没有数据导入');
                }
                
            }else{
                $this->error('请上传Excel文件');
            }
        }
    }

    //AB添加
    public function abExcelAdd()
    {
        if($this->request->isPost()){

            $excel_name = $this->request->param('excel_name');
            $excel_url = $this->request->param('excel_url');

            if(!empty($excel_name) && !empty($excel_url)){

                $excel_array = $this->excelRead($excel_url);

                if ($excel_array[0][0] != '正式交易信息下载') {
                    $this->error('确认表格是否正确');
                }

                array_shift($excel_array);  //删除第一个数组(总标题);
                array_shift($excel_array);  //删除第二个数组(标题);
                
                //查库，去掉重复录入的数据
                $mysql_data = Db::name('ab_excel')->field('ab_order_num')->select()->toArray();
                if ($mysql_data) {
                    foreach ($mysql_data as $k => $v) {
                        $all_data[] = $v['ab_order_num'];
                    }
                }
                unset($mysql_data,$k,$v);

                foreach ($excel_array as $key => $value) {
                    //已有数据去掉
                    if (isset($all_data) && in_array($value[1], $all_data)) {
                        continue;
                    }

                    //去掉失败数据
                    if ($value[6] == '失败') {
                        continue;
                    }

                    $data[] = array(
                        'ab_order_num'      => $value[1],
                        'ab_order_price'    => $value[4],
                        'email'             => $value[16],
                        'status'            => 0,
                    );
                }           

                //Db::name('ab_excel')->where('status',1)->update(['status' => 0]);
                if (isset($data)) {
                    Db::name('ab_excel')->insertAll($data);
                    $this->success('导入成功' , url("asiabill/index"));
                }else{
                    $this->error('没有数据导入');
                }
                
            }else{
                $this->error('请上传Excel文件');
            }
        }
    }

    //物流添加
    public function logisticsExcelAdd()
    {
        if($this->request->isPost()){

            $excel_name = $this->request->param('excel_name');
            $excel_url = $this->request->param('excel_url');

            if(!empty($excel_name) && !empty($excel_url)){

                $excel_array = $this->excelRead($excel_url);

                if ($excel_array[0][0] != '物流方式（中文）') {
                    $this->error('确认表格是否正确');
                }

                array_shift($excel_array);  //删除第一个数组(总标题);

                //每行进行补全，空白行用上行数据填充
                foreach ($excel_array as $key => $value) {
                    foreach ($value as $k => $v) {
                        if ($key != 0 && $v == null) {
                            $excel_array[$key][$k] = $excel_array[$key-1][$k];
                        }
                    }
                }
                unset($key,$value,$k,$v);

                $logistics = new LogisticsExcelModel;
                $count  = $logistics->count();

                foreach ($excel_array as $key => $value) {
                    if ($count && $count >= $key + 1) {
                        $data[] = array(
                            'id'    => $key + 1,
                            'name'  => $value[0],
                            'url'   => $value[2],
                        );
                    }else{
                        $data[] = array(
                            'name'  => $value[0],
                            'url'   => $value[2],
                        );
                    }   
                }           
                
                $logistics->saveAll($data);
                $this->success('导入成功' , url("asiabill/index"));
                
            }else{
                $this->error('请上传Excel文件');
            }
        }
    }

    //AB表处理失败数据
    public function abFalse()
    {
        $ab_falses = Db::name('ab_excel')
                        ->where('status',1)
                        ->order(['id DESC'])
                        ->paginate(20, false, [
                            'query' => request()->param(),
                        ]);
        $page = $ab_falses->render();
        $this->assign("page", $page);
        $this->assign('ab_falses',$ab_falses);

        return $this->fetch();
    }

    //更改AB数据状态
    public function abChangeStatus()
    {
        if (Request::instance()->isGet()){
            $id = input('param.id',0,'intval');
            $re = Db::name('ab_excel')->where('id',$id)->update(['status' => 2]);
            if ($re) {
                $this->success('修改成功');
            }else{
                $this->error('修改失败');
            }
        }
        
    }

    public function excelOut()
    {
        Loader::import("PHPExcel.PHPExcel");
        Loader::import("PHPExcel.PHPExcel.Writer.Excel5");
        Loader::import("PHPExcel.PHPExcel.Writer.Excel2007");
        Loader::import("PHPExcel.PHPExcel.IOFactory");
        $objPHPExcel    = new \PHPExcel();
        $objWriter      = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter      = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', '部门')
        ->setCellValue('B1', '站长')
        ->setCellValue('C1', '提交站数')
        ->setCellValue('D1', '申户数')
        ->setCellValue('E1', '下户站数')
        ->setCellValue('F1', '下户数')
        ->setCellValue('G1', '成功率');
        /*--------------开始从数据库提取信息插入Excel表中------------------*/
        //$i=2; //定义一个i变量，目的是在循环输出数据是控制行数
        $count = count($outAll); //计算有多少条数据
        // echo $count;
        // die;
        for ($i = 2; $i <= $count+1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $outAll[$i-2]['group']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $outAll[$i-2]['stationmaster']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $outAll[$i-2]['total_apply_domain_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $outAll[$i-2]['total_apply_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $outAll[$i-2]['total_down_domain_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $outAll[$i-2]['total_down_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $outAll[$i-2]['success_rate']);
        }
        /*--------------下面是设置其他信息------------------*/

        $objPHPExcel->getActiveSheet()->settitle(date("Y-m-d")); //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //通过PHPExcel_IOFactory的写函数将上面数据写出来
        $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename="站长申户下户数量统计表.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件

    }

    /**
     *读取excel数据
     *string $excel_url  excel路径
     *return array $excel_array 读取数组
     */
    public function excelRead($excel_url)
    {
        vendor('PHPExcel.Classes.PHPExcel');
        vendor('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        vendor('PHPExcel.Classes.PHPExcel.Reader.Excel5');

        $filename = ROOT_PATH.'public/upload/'.$excel_url;  //文件路径
        $extension = cmf_get_file_extension($excel_url);    //文件扩展名
        if($extension == 'xlsx'){
            $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
            $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');  //加载文件内容,编码utf-8
        }else if($extension == 'xls'){
            $objReader =\PHPExcel_IOFactory::createReader('Excel5');
            $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');  //加载文件内容,编码utf-8
        }else{
            $this->error('请上传Excel格式的文件！','');
        }

        $excel_array = $objPHPExcel->getsheet(0)->toArray();   //转换为数组格式

        $excelArray = trimExcelStrBlank($excel_array);
        return TrimArray($excelArray);
    }

	public function index()
	{
		$content = hook_one('admin_asiabill_index_view');
		if (!empty($content)) {
			return $content;
		}
		return $this->fetch();
	}
}
